from zope.interface import implements
import datetime
from twisted.plugin import IPlugin
from twisted.internet import reactor

from sqlalchemy import Column, ForeignKey, Unicode, Text, Integer, Boolean
from sqlalchemy import Float, String, DateTime, engine, and_
from sqlalchemy.databases.mysql import MSBigInteger
from sqlalchemy.databases.postgres import PGBigInteger
from sasync.database import AccessBroker, transact
from sqlalchemy.orm import mapper, relation
from datetime import datetime

from twisted.internet import defer

from pyhsrv.db.types import Node, Server, Plugin, Action, Value, User, Group
from pyhsrv.db.types import Item, Event, vtypes, stypes, draws
from pyhsrv.interfaces import IDBClass
from pyhsrv import errors

from time import time

VERBOSE = False

if VERBOSE:
    import logging
    logging.basicConfig()
    logging.getLogger('engine').setLevel(logging.DEBUG)

class SasyncDBBackend(AccessBroker):
    
    implements(IPlugin, IDBClass)

    def __getattribute__(self, name):
        # For debugging, to see what session is stored
        result = AccessBroker.__getattribute__(self, name)
        if VERBOSE and name == 'session':
            print "SESSION:", result
        return result

    def startup(self):

        def gotTables(null):
            """Mapping the tables to the classes"""
            
            mapper(Node, self.nodes,  properties =
                   dict(plugins = relation(Plugin, backref = 'nodes')))

            mapper(Server, self.servers, properties =
                   dict(nodes = relation(Node, secondary = self.node_server,\
                                         backref = 'servers'),
                        parents = relation(Server, secondary = \
                                           self.server_parents,
                            primaryjoin = self.server_parents.c.parent_id ==\
                                            self.servers.c.server_id,
                            secondaryjoin = self.server_parents.c.child_id ==\
                                            self.servers.c.server_id,
                            backref = 'children')))

            
            mapper(Plugin, self.plugins, properties =
                   dict(items = relation(Item, backref = 'plugin')))
                        

            mapper(Item, self.items, properties =
                   dict(value  = relation(Value, backref = 'item'),
                        events  = relation(Event, backref = 'item')))

            mapper(Value, self.value)

            mapper(Action, self.actions, properties =
                   dict(events = relation(Event, backref = 'action')))
            
            mapper(Event, self.events)

            mapper(User, self.user, properties =
                    dict(_password = self.user.c.password))

            mapper(Group, self.groups, properties =
                    dict(users = relation(User,secondary = self.user_group,\
                                          backref='groups'),
                         actions = relation(Action, backref = 'groups')))
        """nodes table:
        node_id   : the primary key, integer
        name      : the name of the node; it identifies itself with it, and this
                    name is shown in the web interface
        addr      : the node's ip address
        port      : the port of the service
        proto     : 0 : default PyHMon protocol, 1 : IRC, 2 : SNMP,
                    3-9: reserved for future use. 10 + x: x over SSL
        status    : 0 - online, 1 - unreachable, 2 - inactive"""
        d = self.table('nodes',
                Column('node_id', Integer, primary_key = True),
                Column('name', Unicode(40), unique = True),
                Column('addr', Unicode(50)),
                Column('port', Integer),
                Column('proto', Integer),
                Column('status', Integer))

        """servers table:
        server_id : the primary key, integer
        name      : the server's name
        addr      : the ipv4 address
        port      : the port of the service
        proto     : see nodes.proto
        status    : see nodes.status"""
        d.addCallback(lambda _: self.table('servers',
                Column('server_id', Integer, primary_key = True),
                Column('name', Unicode(30), unique = True),
                Column('addr', Unicode(50), unique = True),
                Column('port', Integer),
                Column('alertport', Integer),
                Column('proto', Integer),
                Column('status', Integer)))
 
        """node_server table:
        describes which node connects to which server"""
        d.addCallback(lambda _: self.table('node_server',
            Column('node_id', Integer, ForeignKey('nodes.node_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE')),
            Column('server_id', Integer, ForeignKey('servers.server_id',
                                                    onupdate='CASCADE',
                                                    ondelete = 'CASCADE'))))
        
        """server_parent table:
        describes which server is which server's parent or child"""
        d.addCallback(lambda _: self.table('server_parents',
            Column('parent_id', Integer, ForeignKey('servers.server_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE')),
            Column('child_id', Integer, ForeignKey('servers.server_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE'))))
        
        """groups table:
        it's created and filled by the web interface, here we are using it for
        the alerting actions"""
        d.addCallback(lambda _: self.table('groups',
                Column('group_id', Integer, primary_key = True),
                Column('group_name', Unicode(16), unique = True),
                Column('display_name', Unicode(255)),
                Column('created', DateTime, default = datetime.now)))

        """plugins table:
        information about the plugins
        plugin_id : primary key
        node_id   : the plugin's owner node's id
        vtype     : value type: 0 : integer, 1 : float, 2 : string
        delay     : the time amount between two measurements (in seconds)
        buffered  : if it's zero, the plugin's data isn't buffered on the node;
                    if it isn't zero, it's the multiplicator
        lasttime  : when the last value were received
        name      : the plugin's name
        title     : title
        """
        d.addCallback(lambda _: self.table('plugins',
                Column('plugin_id', Integer, primary_key = True),
                Column('node_id', Integer, ForeignKey("nodes.node_id")),
                Column('vtype', Integer),
                Column('delay', Integer),
                Column('buffered', Integer),
                Column('snmp_oid', String(60)),
                Column('snmp_community', String(60)),
                Column('lasttime', Integer),
                Column('name', Unicode(30)),
                Column('status', Boolean),
                Column('title', Unicode(40))))

        """items talbe:
        we are storing here the properties of the data sources.
        item_id   : the primary key, int
        name      : the item's name
        stype     : source type: 0 : gauge, 1 : counter, 2 : derive, 3 : abs.
        label     : the item's label on the graphs
        cdef      : for the rrd
        plugin_id : which plugin provides the data"""
        d.addCallback(lambda _: self.table('items',
                Column('item_id', Integer, primary_key = True),
                Column('name', Unicode(30)),
                Column('stype', Integer),
                Column('label', Unicode(40)),
                Column('cdef', String(40)),
                Column('draw', String(10)),
                Column('alert_min', Integer),
                Column('alert_max', Integer),
                Column('plugin_id', Integer, ForeignKey("plugins.plugin_id"))))

        """actions table:
        action_id : primary key
        method    : what to do... 0 : send email
        group_id  : which group should receive the message
        msg       : message to send
        intervall : min. intervall between actions
        """
        d.addCallback(lambda _: self.table('actions',
                Column('action_id', Integer, primary_key = True),
                Column('method', Unicode(20)),
                Column('group_id', Integer, ForeignKey("groups.group_id")),
                Column('event_type', Unicode(30)),
                Column('msg', Unicode(255))))

        """events table:
        event_id   : primary key
        event_type : for example alert_min
        item_id    : which item generated the alert
        action_id  : which action was done
        lasttime   : last time this action was active
        """
        d.addCallback(lambda _: self.table('events',
                Column('event_id', Integer, primary_key = True),
                Column('event_type', Unicode(40)),
                Column('item_id', Integer, ForeignKey("items.item_id")),
                Column('action_id', Integer, ForeignKey("actions.action_id")),
                Column('lasttime', Integer)))
        """value table:
        timestamp : when the value was measured
        item_id   : which data source
        value_*   : the measured data
        """
        if self.engineParams[0].split(':')[0] == "mysql":
            d.addCallback(lambda _: self.table('value',
                    Column('value_id', Integer, primary_key = True),
                    Column('timestamp', Integer),
                    Column('item_id', Integer, ForeignKey("items.item_id")),
                    Column('value_int', MSBigInteger),
                    Column('value_flt', Float),
                    Column('value_str', String(255))))
        elif self.engineParams[0].split(':')[0] == "postgres":
            d.addCallback(lambda _: self.table('value',
                    Column('value_id', Integer, primary_key = True),
                    Column('timestamp', Integer),
                    Column('item_id', Integer, ForeignKey("items.item_id")),
                    Column('value_int', PGBigInteger),
                    Column('value_flt', Float),
                    Column('value_str', String(255))))
        else:
            d.addCallback(lambda _: self.table('value',
                    Column('value_id', Integer, primary_key = True),
                    Column('timestamp', Integer),
                    Column('item_id', Integer, ForeignKey("items.item_id")),
                    Column('value_int', Integer),
                    Column('value_flt', Float),
                    Column('value_str', String(255))))

        """users table:
        this table is filled and used by the web interface, we are using only
        the email address and phone number"""
        d.addCallback(lambda _: self.table('user',
                Column('user_id', Integer, primary_key = True),
                Column('user_name', Unicode(16), unique = True),
                Column('email_address', Unicode(255), unique = True),
                Column('phone_num', Unicode(16), unique = True),
                Column('display_name', Unicode(255)),
                Column('password', Unicode(64)),
                Column('created', DateTime, default=datetime.now)))

        """user_group table:
        tells if a user is a member of a group"""
        d.addCallback(lambda _: self.table('user_group',
            Column('user_id', Integer, ForeignKey('user.user_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE')),
            Column('group_id', Integer, ForeignKey('groups.group_id',
                                                   onupdate='CASCADE',
                                                   ondelete = 'CASCADE'))))

        d.addCallback(gotTables)
        d.addErrback(errors.catchError)
        return d
        
    @transact
    def getNodes(self, srvname):
        srv = Server.by_name(self.session, srvname)
        if srv != None:
            return srv.nodes
        else:
            return []
    
    @transact
    def getNodes_by_srvid(self, srvid):
        srv = Server.by_id(self.session, srvid)
        if srv != None:
            nodes = {}
            for n in srv.nodes:
                nodename = n.name
                nodes[nodename] = {}
                plgs = self.getPlugins(n.node_id)
                for p in plgs:
                    plugname = p.name
                    nodes[nodename][plugname] = []
                    for i in p.items:
                        nodes[nodename][plugname].append(i.name)
            return nodes
        else:
            return {}

    @transact
    def getNodes_by_name(self, srvname):
        srv = Server.by_name(self.session, srvname)
        if srv != None:
            nodes = {}
            for n in srv.nodes:
                nodename = n.name
                nodes[nodename] = {}
                plgs = self.getPlugins(n.node_id)
                for p in plgs:
                    plugname = p.name
                    nodes[nodename][plugname] = []
                    for i in p.items:
                        nodes[nodename][plugname].append(i.name)
            return nodes
        else:
            return {}

    @transact
    def getNodesWithStatus(self, srvname):
        ret = {}
        srv = Server.by_name(self.session, srvname)
        for n in srv.nodes:
            ret[n.name] = n.status
        return ret
    
    @transact
    def getChildren(self, server):
        srv = self.session.query(Server).filter(Server.name == \
                                                unicode(server)).first()
        if srv != None:
            return srv.children
        else:
            return []
    
    @transact
    def getParents(self, server):
        srv = self.session.query(Server).filter(Server.name == \
                                                unicode(server)).first()
        if srv != None:
            return srv.parents
        else:
            return []
    
    @transact
    def getNode_by_id(self, nodeid):
        node = self.session.query(Node).filter_by(node_id = nodeid).first()
        return node
    
    @transact
    def getServer_by_id(self, srvid):
        return Server.by_id(self.session, srvid)
    
    @transact
    def getPlugins(self, nodeid):
        plugs = self.session.query(Node).filter_by(node_id =\
                                                   nodeid).first().plugins
        return plugs
    
    @transact
    def getPlugin_by_name(self, x, nodename, plugname):
        n = Node.by_name(self.session, nodename)
        if n != None:
            p = Plugin.by_node_and_plug(self.session, n.node_id, plugname)
            if p != None:
                ret = {}
                ret['name']     = p.name
                ret['title']    = p.title
                ret['vtype']    = vtypes[p.vtype]
                ret['buffered'] = p.buffered
                ret['delay']    = p.delay
                return ret             

    @transact
    def getItem(self, x, nodename, plugname, itemname):
        nodeid = Node.by_name(self.session, nodename).node_id
        plugid = Plugin.by_node_and_plug(self.session, nodeid,\
                                         plugname).plugin_id
        itm = Item.by_plug_and_name(self.session, plugid, itemname)
        ret = {}
        ret['name'] = itm.name
        ret['stype'] = itm.stype
        ret['label'] = itm.label
        ret['cdef'] = itm.cdef
        ret['draw'] = itm.draw
        ret['amax'] = itm.alert_max
        ret['amin'] = itm.alert_min
        
        return ret
 
 
    @transact
    def getValues(self, plugid, tstmp):
        ret_list = []
        plug = self.session.query(Plugin).filter_by(plugin_id =\
                                                    plugid).first()
        vtyp = plug.vtype
        ret = {}
        for i in plug.items:
            val = self.session.query(Value).filter_by(item_id =\
                                    i.item_id).filter(timestamp > tstmp).all()
            for v in val:
                ret[v.timestamp] = {}
            for v in val:
                if vtyp == 0:
                    ret[v.timestamp][v.name] = v.value_int
                elif vtyp == 1:
                    ret[v.timestamp][v.name] = v.value_flt
                else:
                    ret[v.timestamp][v.name] = v.value_str
        return ret
    
    @transact
    def getLastValues(self, plugid):
        plug = Plugin.by_id(self.session, plugid)
        vtyp = plug.vtype
        ret = {}
        for i in plug.items:
            val = self.session.query(Value).filter(Value.item_id ==\
                                i.item_id).order_by(timestamp.desc()).first()
            if vtyp == 0:
                ret[i] = val.value_int
            elif vtyp == 1:
                ret[i] = val.value_flt
            else:
                ret[i] = val.value_str
            time = val.timestamp
        return [time, ret]

    
    @transact
    def getActionForAlert(self, event_type, nodename, plugname, itemname):
        ret = {}
        n = Node.by_name(self.session, nodename)
        acts = []
        if n != None:
            p = Plugin.by_node_and_plug(self.session, n.node_id, plugname)
            if p != None:
                i = Item.by_plug_and_name(self.session, p.plugin_id, itemname)
                if i != None:
                    event = Event.by_itm_and_type(self.session,
                                                  i.item_id,
                                                  unicode(event_type))
                    if event != None:
                        if time.now() - event.lasttime > 180:
                            event.lasttime = int(time.now)
                            acts += self.session.query(Action).filter(\
                                Action.event_type == unicode(event_type)).all()
        acts += self.session.query(Action).filter(Action.event_type == \
                                            unicode('alert_any')).all()
        for a in acts:
            ret[a.method] = []
            ret[a.method].append(a.groups.group_name)
            ret[a.method].append(a.msg)
        return ret
            
    @transact
    def insertValue(self, nodeid, plugname, itemname, timestmp, value):
        plug = Plugin.by_node_and_plug(self.session, nodeid, plugname)
        itemid = Item.by_plug_and_name(self.session, plug.plugin_id,\
                                       itemname).item_id
        val = Value()
        val.timestamp = int(timestmp)
        val.item_id = itemid
        if plug.vtype == 0:
            val.value_int = int(value)
        elif plug.vtype == 1:
            val.value_flt = float(value)
        else:
            val.value_str = value
        self.session.add(val)
        
    @transact
    def insertValue_by_name(self, nodename, plugname, itemname, timestmp,
                                value):
        n = Node.by_name(self.session, unicode(nodename))
        if n != None:
            plug = Plugin.by_node_and_plug(self.session,
                                           n.node_id,
                                           unicode(plugname))
            if plug != None:
                itm = Item.by_plug_and_name(self.session,
                                            plug.plugin_id,
                                            unicode(itemname))
                if itm != None:
                    old_value = self.session.query(Value).filter(\
                        and_(Value.item_id == itm.item_id,
                             Value.timestamp == int(timestmp))).first()
                    if old_value == None:
                        val = Value()
                        val.timestamp = int(timestmp)
                        val.item_id = itm.item_id
                        if plug.vtype == 0:
                            val.value_int = int(value)
                        elif plug.vtype == 1:
                            val.value_flt = float(value)
                        else:
                            val.value_str = value
                        self.session.add(val)

    @transact
    def insertPlugin(self, nodeid, plugname, params):
        if Plugin.by_node_and_plug(self.session, nodeid, \
                                       unicode(plugname)) == None:
            P = Plugin()
            P.name = unicode(plugname)
            P.node_id = nodeid
            P.vtype = vtypes.index(params['vtype'])
            P.buffered = params['buffered']
            if params.has_key('delay'):
                P.delay = params['delay']
            else:
                P.delay = 180
            if params.has_key('title'):
                P.title = unicode(params['title'])
            P.lasttime = 0
            P.status = False
            P.snmp_oid = ""
            P.snmp_community = ""
            self.session.add(P)
        
    @transact
    def insertPlugin_by_name(self, nodename, plugname, params):
        n = Node.by_name(self.session, nodename)
        if n != None:
            if Plugin.by_node_and_plug(self.session, n.node_id, \
                                       unicode(plugname)) == None:
                P = Plugin()
                P.name = unicode(plugname)
                P.node_id = n.node_id
                P.vtype = vtypes.index(params['vtype'])
                P.buffered = int(params['buffered'])
                if params.has_key('delay'):
                    P.delay = int(params['delay'])
                else:
                    P.delay = 180
                if params.has_key('title'):
                    P.title = unicode(params['title'])
                P.lasttime = 0
                P.status = False
                P.snmp_oid = ""
                P.snmp_community = ""
                self.session.add(P)

    @transact
    def insertItem(self, nodeid, plugname, itemname, params):
        plug = Plugin.by_node_and_plug(self.session, nodeid, plugname)
        if plug != None:
            if Item.by_plug_and_name(self.session, plug.plugin_id, \
                                             unicode(itemname)) == None:
                I = Item()
                I.name = unicode(itemname)
                I.plugin_id = plug.plugin_id
                I.stype = stypes.index(params['stype'])
                I.label = unicode(params['label'])
                if params.has_key('draw'):
                    I.draw = params['draw']
                if params.has_key('cdef'):
                    I.cdef = params['cdef']
                if params.has_key('amin'):
                    I.alert_min = params['amin']
                if params.has_key('amax'):
                    I.alert_max = params['amax']
         
                self.session.add(I)
        
    @transact
    def insertItem_by_name(self, nodename, plugname, itemname, params):
        n = Node.by_name(self.session, nodename)
        if n != None:
            plug = Plugin.by_node_and_plug(self.session, n.node_id, \
                                           unicode(plugname))
            if plug != None:
                if Item.by_plug_and_name(self.session, plug.plugin_id, \
                                         unicode(itemname)) == None:
                    I = Item()
                    I.name = unicode(itemname)
                    I.plugin_id = plug.plugin_id
                    if params.has_key('stype'):
                        I.stype = params['stype']
                    if params.has_key('label'):
                        I.label = unicode(params['label'])
                    if params.has_key('draw'):
                        I.draw = params['draw']
                    if params.has_key('cdef'):
                        I.cdef = params['cdef']
                    if params.has_key('amin'):
                        I.alert_min = params['amin']
                    if params.has_key('amax'):
                        I.alert_max = params['amax']
                    self.session.add(I)

    @transact
    def updateNodeAddr(self, name, addr):
        N = Node.by_name(self.session, unicode(name))
        N.addr = unicode(addr)

    @transact
    def updateNodeStatus(self, nodeid, status):
        N = Node.by_id(self.session, nodeid)
        N.status = status
        
    @transact
    def updateNodeStatus_by_name(self, nodename, status):
        N = Node.by_name(self.session, nodename)
        N.status = status
    
    @transact
    def getNodeStatus(self, nodename):
        N = Node.by_name(self.session, nodename)
        if N != None:
            return N.name + ":" + str(N.status)
        else:
            return None
    
    @transact
    def updateServerStatus(self, srvid, status):
        S = Server.by_id(self.session, srvid)
        S.status = status
        
    @transact
    def updateSrvStatus(self, nodeid, status):
        S = Server.by_id(self.session, nodeid)
        S.status = status
        
    @transact
    def updatePluginLast(self, nodeid, plugname, last):
        P = Plugin.by_node_and_plug(self.session, nodeid, plugname)
        P.lasttime = last
        
    @transact
    def updatePluginLast_by_name(self, nodename, plugname, last):
        nodeid = Node.by_name(self.session, nodename).node_id
        P = Plugin.by_node_and_plug(self.session, nodeid, plugname)
        P.lasttime = last
        
    @transact
    def updatePluginStatus(self, nodenid, plugname, newstatus):
        P = Plugin.by_node_and_plug(self.session, nodeid, plugname)
        P.status = 0 if newstatus == "active" else 1
        
    @transact
    def addNodeToServer(self, nodename, srvid, status):
        N = Node.by_name(self.session, nodename)
        if N == None:
            N = Node()
            N.name = nodename
            N.addr = ""
            N.port = 0
            N.proto = 0
            N.status = status
            self.session.add(N)
        S = Server.by_id(self.session, srvid)
        S.nodes.append(N)
        
    @transact
    def updateNodeAddress(self, nodename, addr):
        N = Node.by_name(self.session, nodename)
        N.addr = addr
        
    @transact
    def getLastTime(self, nodename, plugname):
        nodeid = Node.by_name(self.session, nodename).node_id
        P = Plugin.by_node_and_plug(self.session, nodeid, plugname)
        return P.lasttime
    
    @transact
    def getValuesForPlugin(self, nodename, plguname, tstmp):
        """
        returns a dictionary, in the following format:
        ret = {'item1':[[value1,time1],[value2,time2]...], item2:...}
        """
        ret = {}
        n = Node.by_name(self.session, nodename)
        if n != None:
            p = Plugin.by_node_and_plug(self.session, n.node_id, plguname)
            if p != None:
                for i in p.items:
                    ret[i.name] = []
                    vs = self.session.query(Value).filter\
                       (and_(Value.item_id == i.item_id,
                             Value.timestamp > tstmp)).\
                       order_by(Value.timestamp).limit(100).all()
                    for v in vs:
                        if p.vtype == 0:
                            ret[i.name].append([v.value_int, v.timestamp])
                        elif p.vtype == 1:
                            ret[i.name].append([v.value_flt, v.timestamp])
                        elif p.vtype == 2:
                            ret[i.name].append([v.value_str, v.timestamp])
                return ret
    
    @transact
    def getMailAddrsForGroup(self, group):
        """ returns the email addresses of the users of group 'group' """
        ret = []
        g = Group.by_name(self.session, group)
        if g != None:
            for u in g.users:
                ret.append(u.email_address)
        return ret
